package com.hwua.dao.impl;

import com.hwua.dao.StudentDao;
import com.hwua.entity.Student;
import com.hwua.entity.Teacher;
import com.hwua.util.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author CQY
 * 实现学生接口
 */
public class StudentDaoImpl implements StudentDao{
    @Override
    public int save(Student student) {
        String sql = "insert into student(stuid,stuname,stuage,stusex) values(?,?,?,?)";
        Object[] params = {student.getStuId(),student.getStuName(),student.getStuAge(),student.getStuSex()};
        int row = 0;
        try {
            row = DBHelper.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int update(Student student) {
        String sql = "update student set stuname=?,stuage=?,stusex=? where stuid=?";
        Object[] params = {student.getStuName(),student.getStuAge(),student.getStuSex(),student.getStuId()};
        int row = 0;
        try {
            row = DBHelper.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int delete(String id)  {
        Connection conn=null;
        String sql = "delete from student where stuid=?";
        String sql1 = "delete from score where stuid=?";
        PreparedStatement pre= null;
        int row=0;
        try {
            conn=DBHelper.getConnection();
            conn.setAutoCommit(false);
            pre = conn.prepareStatement(sql);
            pre.setString(1,id);
            row= pre.executeUpdate();
            pre=conn.prepareStatement(sql);
            pre.setString(1,id);
            row+= pre.executeUpdate();
             conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }

        return row;
    }

    @Override
    public int deleteBatch(String[] id) {
        int row=0 ;
        Connection conn=DBHelper.getConnection();
        String sql = "delete from student where stuid=?";

        try {
            PreparedStatement pre=conn.prepareStatement(sql);
            for(int i=0;i<id.length;i++){
                pre.setString(1,id[i]);
                //添加参数到批处理
                pre.addBatch();
            }
            //执行批处理
            int [] rows= pre.executeBatch();
            //统计删除行数
            for (int i = 0; i <rows.length ; i++) {
                row+=rows[i];
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return row;
    }

    @Override
    public Student findById(String id) {
        Connection conn = DBHelper.getConnection();
        Student student = null;
        String sql = "select * from student where stuid=?";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            pre.setString(1, id);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                student = new Student();
                student.setStuId(rs.getString("stuid"));
                student.setStuName(rs.getString("stuname"));
                student.setStuAge(rs.getString("stuage"));
                student.setStuSex(rs.getString("stusex"));

            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }

    @Override
    public List<Student> findAll() {
        Connection conn = DBHelper.getConnection();
        List<Student> list = new ArrayList<Student>();
        Student student = null;
        String sql = "select * from student order by stuid asc ";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                student = new Student();
                student.setStuId(rs.getString("stuid"));
                student.setStuName(rs.getString("stuname"));
                student.setStuAge(rs.getString("stuage"));
                student.setStuSex(rs.getString("stusex"));
                list.add(student);

            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public List<Student> findSearch(String keyword) {
        Connection conn = DBHelper.getConnection();
        List<Student> list = new ArrayList<Student>();
        Student student = null;
        String sql = "select * from student where stuname like '%"+keyword+"%' order by stuid asc ";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                student = new Student();
                student.setStuId(rs.getString("stuid"));
                student.setStuName(rs.getString("stuname"));
                student.setStuAge(rs.getString("stuage"));
                student.setStuSex(rs.getString("stusex"));
                list.add(student);

            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return list;
    }
}
